package com.wgworkshop.pems.repository;

import com.wgworkshop.pems.model.ClassHourViewRecord;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

public class ClassHourRepositoryImpl implements ClassHourRepositoryCustom {
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Iterable<ClassHourViewRecord> getClassHourView(String name) {
        String sql = "SELECT s.id AS id, s.code AS code, s.name AS name, c.class_hour AS total_class_hour, " +
                "c.cost AS cost, a.cnt AS used_class_hour, ca.start_date AS start_date, ca.class_num AS class_num " +
                "FROM student AS s " +
                "LEFT JOIN " +
                "(SELECT student_id, SUM(class_hour) AS class_hour, SUM(cost) AS cost " +
                "FROM class_hour GROUP BY student_id) AS c " +
                "ON s.id = c.student_id " +
                "LEFT JOIN " +
                "(SELECT student_id, 2 * COUNT(id) AS cnt " +
                "FROM attendance GROUP BY student_id) AS a " +
                "ON s.id = a.student_id " +
                "LEFT JOIN class_arrange AS ca " +
                "ON s.id = ca.student_id " +
                "AND ca.status = 1 " +
                "WHERE s.name LIKE :name " +
                "AND s.status = 1 " +
                "ORDER BY s.code DESC ";
        Query query = entityManager.createNativeQuery(sql, "ClassHourViewRecordMapping");
        query.setParameter("name", "%" + name + "%");
        return query.getResultList();
    }

    @Override
    public Iterable<ClassHourViewRecord> getAttendanceClassHourInfo(int week, int classNum) {
        String sql = "SELECT s.id AS id, s.code AS code, s.name AS name, c.class_hour AS total_class_hour, " +
                "c.cost AS cost, a.cnt AS used_class_hour, ca.start_date AS start_date, ca.class_num AS class_num " +
                "FROM " +
                "(SELECT student_id, SUM(class_hour) AS class_hour, SUM(cost) AS cost " +
                "FROM class_hour GROUP BY student_id HAVING class_hour > 0) AS c " +
                "LEFT JOIN " +
                "(SELECT student_id, 2 * COUNT(id) AS cnt " +
                "FROM attendance GROUP BY student_id) AS a " +
                "ON c.student_id = a.student_id " +
                "INNER JOIN class_arrange AS ca " +
                "ON c.student_id = ca.student_id " +
                "AND DATE_FORMAT(ca.start_date, '%w') = :week " +
                "AND ca.class_num = :classNum " +
                "AND ca.status = 1 " +
                "LEFT JOIN student AS s " +
                "ON c.student_id = s.id " +
                "WHERE s.status = 1 " +
                "ORDER BY s.code DESC ";
        Query query = entityManager.createNativeQuery(sql, "ClassHourViewRecordMapping");
        query.setParameter("week", week);
        query.setParameter("classNum", classNum);
        return query.getResultList();
    }
}
